package com.sapient;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class MappingApprovalServlet
 */
@WebServlet("/MappingApprovalServlet")
public class MappingApprovalServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public MappingApprovalServlet() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		try {		
			String[][] result=null;
			int count = 0;
			String url = "jdbc:oracle:thin:@10.207.60.85:1521/XE";
			String username = "surv";
			String password = "surv";
			String[] mapid;

			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection(url, username,password);
			Statement st = conn.createStatement();

			HttpSession session= request.getSession(true);



			int rows=0;		
			String SQL="Select * from entityMapping where status='P'";// Need to create Mapping Approval table in DB(Walker)
			ResultSet rs=st.executeQuery(SQL);
			while(rs.next()){
				rows++;
			}
			result = new String[rows][7];
			mapid = new String[rows];
			rs=st.executeQuery(SQL);

			while(rs.next())
			{
				System.out.println(rs.getString("mapid"));
				mapid[count] = rs.getString("mapid");
				result[count][0] = rs.getString("mapid");
				result[count][1] = rs.getString("mappingstring");
				result[count][2] = rs.getString("mappingentity");
				result[count][3] = rs.getString("startdate");
				result[count][4] = rs.getString("sourcesystem");
				result[count][5] = rs.getString("enddate");
				result[count][6] = rs.getString("status");

				count++; 
				System.out.println(rs.getString("mapid")+ rs.getString(2));
			}

			request.setAttribute("result", result);
			session.setAttribute("mapid", mapid);
			RequestDispatcher r = getServletContext().getRequestDispatcher("/MappingApproval.jsp");
			r.include(request, response);

			conn.close();

		}
		catch (Exception e) {
			System.err.println("Got an exception! ");
			System.err.println(e.getMessage());
			e.printStackTrace();
		}




	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		//String[] status=request.getParameterValues("status");
		HttpSession session= request.getSession(true);

		String[] mapid =(String[]) session.getAttribute("mapid");
		for(String s: mapid){
			String value =(String) request.getParameter(s);
			if(value!=null){
				try
				{ 
					Class.forName("oracle.jdbc.driver.OracleDriver");
					Connection conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
					Statement st = conn.createStatement(); 

					String SQL = "";
					String mString = "";
					String mEntity = "";
					String sDate = "";
					String sSys = "";
					String eType = "";
					String iEnable = "";

					SQL = "select MappingString, MappingEntity, StartDate, SourceSystem,entitytype,isenabled from entitymapping where mapid = "+Integer.parseInt(s);
					ResultSet rs=st.executeQuery(SQL);
					while(rs.next()){
						mString = rs.getString(1);
						mEntity = rs.getString(2);
						sDate = rs.getString(3).substring(0, 10);
						sSys = rs.getString(4);
						eType = rs.getString(5);
						iEnable = rs.getString(6);
					}
					if(value.equals("A")){
						SQL = "INSERT INTO EntityMapping (MapId, MappingString, MappingEntity, StartDate, SourceSystem,entitytype,isenabled,status) VALUES (mappingseq.nextval,'"+ mEntity + "','" + mString + "'," + "to_date('" + sDate + "','RRRR-MM-DD')" + ",'" + sSys + "','" + eType + "','" + iEnable + "','A')";
						st.executeUpdate(SQL);
						System.out.println(SQL);
					}
					SQL = "update entitymapping set  status ='"+value+"' where mapid = "+Integer.parseInt(s);
					st.executeUpdate(SQL);

					conn.close(); 
				} catch (Exception e) { 
					System.err.println("Got an exception! "); 
					System.err.println(e.getMessage()); 
					e.printStackTrace();
				} 


			}

		}
		RequestDispatcher r = getServletContext().getRequestDispatcher("/MainApproval.jsp");
		r.forward(request, response);

	}

}
